package com.ruiyi.subway.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelExportUtil {
	public static HSSFWorkbook generateExcel(List<Map<String, Object>> list, String title,String sheetName) {
		HSSFWorkbook book = new HSSFWorkbook();
		try {
			
			HSSFSheet sheet = book.createSheet(sheetName);
			//sheet.autoSizeColumn(1, true);// 自适应列宽度
			
			// 样式设置
			HSSFCellStyle style = book.createCellStyle();
			style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 生成一个字体
			HSSFFont font = book.createFont();
			font.setColor(HSSFColor.VIOLET.index);
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 把字体应用到当前的样式
			style.setFont(font);

			HSSFCellStyle style2 = book.createCellStyle();
			style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			// 设置上下左右边框
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			// 填充表头标题
			int colSize = list.get(0).entrySet().size();
			// 合并单元格供标题使用(表名)
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSize - 1));
			HSSFRow firstRow = sheet.createRow(0);// 第几行（从0开始）
			HSSFCell firstCell = firstRow.createCell(0);
			firstCell.setCellValue(title);
			firstCell.setCellStyle(style);

			// 填充表头header
			HSSFRow row = sheet.createRow(1);
			Set<Entry<String, Object>> set = list.get(0).entrySet();
			List<Entry<String, Object>> l = new ArrayList<Map.Entry<String, Object>>(set);
			for (int i = 0; i < l.size(); i++) {
				String key = l.get(i).getKey();
				System.out.println(key);
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(key);
				cell.setCellStyle(style2);
			}

			// 填充表格内容
			for (int i = 0; i < list.size(); i++) {
				HSSFRow row2 = sheet.createRow(i + 2);// index：第几行
				sheet.setColumnWidth(i, 15* 256);  
				Map<String, Object> map = list.get(i);
				Set<Entry<String, Object>> set2 = map.entrySet();
				List<Entry<String, Object>> ll = new ArrayList(set2);
				for (int j = 0; j < ll.size(); j++) {
					Object val = ll.get(j).getValue();
					String varStr = val.toString();
					HSSFCell cell = row2.createCell(j);// 第几列：从0开始
					cell.setCellValue(varStr);
					cell.setCellStyle(style2);
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return book;
	}
	public static HSSFWorkbook generateExcel(List<Map<String, Object>> list, String title,String sheetName,List<String> tit) {
		HSSFWorkbook book = new HSSFWorkbook();
		try {
			
			HSSFSheet sheet = book.createSheet(sheetName);
			//sheet.autoSizeColumn(1, true);// 自适应列宽度
			
			// 样式设置
			HSSFCellStyle style = book.createCellStyle();
			style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
			style.setFillPattern(HSSFCellStyle.NO_FILL);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
			// 生成一个字体
			HSSFFont font = book.createFont();
			font.setColor(HSSFColor.VIOLET.index);
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 把字体应用到当前的样式
			style.setFont(font);
			
			HSSFCellStyle style2 = book.createCellStyle();
			style2.setFillPattern(HSSFCellStyle.NO_FILL);
			// 设置上下左右边框
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style2.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
			
			// 填充表头标题
			int colSize = list.get(0).entrySet().size();
			// 合并单元格供标题使用(表名)
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSize - 1));
			HSSFRow firstRow = sheet.createRow(0);// 第几行（从0开始）
			HSSFCell firstCell = firstRow.createCell(0);
			firstCell.setCellValue(title);
			firstCell.setCellStyle(style);
			
			// 填充表头header
			HSSFRow row = sheet.createRow(1);
			Set<Entry<String, Object>> set = list.get(0).entrySet();
			if(tit.size()!=0){
				for (int i = 0; i <tit.size(); i++) {
					String key = tit.get(i);
					System.out.println(key);
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(key);
					cell.setCellStyle(style2);
				}
				
				// 填充表格内容
				for (int i = 0; i < list.size(); i++) {
					HSSFRow row2 = sheet.createRow(i + 2);// index：第几行
					sheet.setColumnWidth(i, 15* 256);  
					Map<String, Object> map = list.get(i);
					Set<Entry<String, Object>> set2 = map.entrySet();
					List<Object>lis=new ArrayList<Object>();
					
					List<Entry<String, Object>> ll = new ArrayList(set2);
					for (int j = 0; j <tit.size(); j++) {
						for (Entry<String, Object> entry : ll) {
							String key = entry.getKey();
							Object value = entry.getValue();
							if(key.equals(tit.get(j))){
								lis.add(value);
							}
						}
					}
					
					for (int j = 0; j < lis.size(); j++) {
						
						Object val = lis.get(j);
						
						String varStr = val.toString();
						HSSFCell cell = row2.createCell(j);// 第几列：从0开始
						if(val instanceof Integer || val instanceof Double || val instanceof Float){
							cell.setCellValue(Double.parseDouble(varStr));
						}else{
							cell.setCellValue(varStr);
						}
						
						cell.setCellStyle(style2);
					}
				}
			}else{
				List<Entry<String, Object>> l = new ArrayList<Map.Entry<String, Object>>(set);
				for (int i = 0; i < l.size(); i++) {
					String key = l.get(i).getKey();
					System.out.println(key);
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(key);
					cell.setCellStyle(style2);
				}
				
				// 填充表格内容
				for (int i = 0; i < list.size(); i++) {
					HSSFRow row2 = sheet.createRow(i + 2);// index：第几行
					
					
					
					sheet.setColumnWidth(i, 15* 256);  
					Map<String, Object> map = list.get(i);
					Set<Entry<String, Object>> set2 = map.entrySet();
					List<Entry<String, Object>> ll = new ArrayList(set2);
					for (int j = 0; j < ll.size(); j++) {
					
						Object val = ll.get(j).getValue();
						String varStr = val.toString();
						HSSFCell cell = row2.createCell(j);// 第几列：从0开始
						//row2.createCell(i).setCellValue(Double.parseDouble(value));文本转换为数字
						cell.setCellValue(Double.parseDouble(varStr));
						cell.setCellStyle(style2);
					}
				}
			}
			
			
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return book;
	}


}